insert overwrite table jms_dm.dm_site_wrong_sum_dt partition ( dt )
select
     t.dt  as date_time
    ,max(t.agent_code)  as  agent_code
    ,max(t.agent_name) as agent_name
    ,max(t.fran_code) as fran_code
    ,max(t.fran_name) as fran_name
    ,t.taking_site_code
    ,max(t.taking_site_name) as taking_site_name
    ,count(t.waybill_no) as total_num
    ,if(  count(t.waybill_no) <= max(t1.numbers) ,0
         ,sum(t.is_wrong)
               ) as wrong_num
    ,1 as type_flg
    ,t.dt
from jms_dm.dm_site_wrong_detail_dt t
left join jms_dim.dim_tab_network_delivery_config_base t1 on t.taking_site_code=t1.network_code and t1.state=1
where   t.dt>='{{ execution_date | date_add(-14) | cst_ds }}' and t.dt<='{{ execution_date | cst_ds }}'
group by
t.dt
,taking_site_code
union all
select
     t.dt  as date_time
    ,max(t.taking_agent_code)  as  agent_code
    ,max(t.taking_agent_name) as agent_name
    ,max(t.taking_fran_code) as fran_code
    ,max(t.taking_fran_name) as fran_name
    ,t.taking_code as sign_network_code
    ,max(t.taking_name) as taking_name
    ,count(t.waybill_no) as total_num
    ,sum(t.is_wrong_flg) as wrong_num
    ,2 as type_flg
    ,t.dt
from jms_dm.dm_all_transfer_count_deatil_dt t
left join jms_dim.dim_tab_network_delivery_config_base t1 on t.taking_code=t1.network_code and t1.state=1
where     t.dt>='{{ execution_date | date_add(-14) | cst_ds }}' and t.dt<='{{ execution_date | cst_ds }}' and t.is_reback_flg <>1
group by
t.dt
,taking_code
distribute by dt
;